package com.macro.mall.utils;

import cn.hutool.core.util.StrUtil;
import com.macro.mall.common.utils.IsNeeded;
import com.macro.mall.model.UmsCommunity;
import com.macro.mall.model.UmsMemberLog;
import com.macro.mall.service.UmsCommunityService;
import com.macro.mall.service.UmsMemberLogService;
import com.macro.mall.service.UmsMemberService;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.stereotype.Component;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * excel读取工具类
 *
 * @author dexin
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
@Component
public class ReadExcelUtil {

    private static int totalRows = 0;// 总行数

    private static int totalCells = 0;// 总列数

    private static String errorInfo;// 错误信息
    @Autowired
    private UmsMemberService umsMemberService;
    @Autowired
    private UmsCommunityService umsCommunityService;
    @Autowired
    private UmsMemberLogService umsMemberLogService;
    @Autowired
    private PasswordEncoder passwordEncoder;
    /**
     * 无参构造方法
     */
    public ReadExcelUtil() {
    }

    public static int getTotalRows() {
        return totalRows;
    }

    public static int getTotalCells() {
        return totalCells;
    }

    public static String getErrorInfo() {
        return errorInfo;
    }


    /**
     * 按指定坐标读取实体数据 <按顺序放入带有注解的实体成员变量中>
     *
     * @param wb       工作簿
     * @param t        实体
     * @param in       输入流
     * @param integers 指定需要解析的坐标
     * @return T 相应实体
     * @throws IOException
     * @throws Exception
     * @see [类、类#方法、类#成员]
     */
    @SuppressWarnings("unused")
    public static <T> T readDateT(Workbook wb, T t, InputStream in, Integer[]... integers)
            throws IOException, Exception {
        // 获取该工作表中的第一个工作表
        Sheet sheet = wb.getSheetAt(0);

        // 成员变量的值
        Object entityMemberValue = "";

        // 所有成员变量
        Field[] fields = t.getClass().getDeclaredFields();
        // 列开始下标
        int startCell = 0;

        /** 循环出需要的成员 */
        for (int f = 0; f < fields.length; f++) {

            fields[f].setAccessible(true);
            String fieldName = fields[f].getName();
            boolean fieldHasAnno = fields[f].isAnnotationPresent(IsNeeded.class);
            // 有注解
            if (fieldHasAnno) {
                IsNeeded annotation = fields[f].getAnnotation(IsNeeded.class);
                boolean isNeeded = annotation.isNeeded();

                // Excel需要赋值的列
                if (isNeeded) {

                    // 获取行和列
                    int x = integers[startCell][0] - 1;
                    int y = integers[startCell][1] - 1;

                    Row row = sheet.getRow(x);
                    Cell cell = row.getCell(y);

                    if (row == null) {
                        continue;
                    }

                    // Excel中解析的值
                    String cellValue = getCellValue(cell);
                    // 需要赋给成员变量的值
                    entityMemberValue = getEntityMemberValue(entityMemberValue, fields, f, cellValue);
                    // 赋值
                    PropertyUtils.setProperty(t, fieldName, entityMemberValue);
                    // 列的下标加1
                    startCell++;
                }
            }

        }

        return t;
    }

    /**
     * 读取列表数据 <按顺序放入带有注解的实体成员变量中>
     *
     * @param wb        工作簿
     * @param t         实体
     * @param beginLine 开始行数
     * @param totalcut  结束行数减去相应行数
     * @return List<T> 实体列表
     * @throws Exception
     * @see [类、类#方法、类#成员]
     */
    @SuppressWarnings("unchecked")
    public <T> Map<String, Object> readDataListT(Workbook wb, T t, int sheetAt, int beginLine, int totalcut, Map<String, Object> map) throws Exception {
        List<T> listt = new ArrayList<T>();

        /** 得到第一个shell */
        Sheet sheet = wb.getSheetAt(sheetAt);

        /** 得到Excel的行数 */
        totalRows = sheet.getPhysicalNumberOfRows();

        /** 得到Excel的列数 */
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        boolean flag = false;
        System.out.println("总行数：" + (sheet.getLastRowNum() + 1));
        for (int i = beginLine; i <= sheet.getLastRowNum(); ) {
            Row r = sheet.getRow(i);
            if (r == null) {
                // 如果是空行（即没有任何数据、格式），直接把它以下的数据往上移动
                sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
                continue;
            }
            flag = false;
            for (Cell c : r) {
                if (c.getCellType() != Cell.CELL_TYPE_BLANK) {
                    flag = true;
                    break;
                }
            }
            if (flag) {
                i++;
                continue;
            } else {//如果是空白行（即可能没有数据，但是有一定格式）
                if (i == sheet.getLastRowNum()) {
                    //如果到了最后一行，直接将那一行remove掉
                    sheet.removeRow(r);
                } else {
                    //如果还没到最后一行，则数据往上移一行
                    sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
                }
            }
        }
        totalRows = sheet.getLastRowNum() + 1;

        /** 循环Excel的行 */
        for (int r = beginLine - 1; r < totalRows - totalcut; r++) {

            Object newInstance = t.getClass().newInstance();
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }

            // 成员变量的值
            Object entityMemberValue = "";

            // 所有成员变量
            Field[] fields = t.getClass().getDeclaredFields();
            // 列开始下标
            int startCell = 0;

            for (int f = 0; f < fields.length; f++) {

                fields[f].setAccessible(true);
                String fieldName = fields[f].getName();
                boolean fieldHasAnno = fields[f].isAnnotationPresent(IsNeeded.class);
                // 有注解
                if (fieldHasAnno) {
                    IsNeeded annotation = fields[f].getAnnotation(IsNeeded.class);
                    boolean isNeeded = annotation.isNeeded();
                    // Excel需要赋值的列
                    if (isNeeded) {
                        Cell cell = row.getCell(startCell);
                        String cellValue = getCellValue(cell);
                        switch (startCell) {
                            case 1:
                                if (StringUtils.isEmpty(cellValue)) {
                                    int cellNum = startCell + 1;
                                    map.put("result", "9999");
                                    map.put("message", r + 1 + "行" + cellNum + "列" + ":" + "手机号不可为空");
                                    return map;
                                }
                                String substring = cellValue.substring(0, 1);
                                if (!substring.equals("1") || cellValue.length() != 11) {
                                    int cellNum = startCell + 1;
                                    map.put("result", "9999");
                                    map.put("message", r + 1 + "行" + cellNum + "列" + ":" + "手机号填写不规范");
                                    return map;
                                }
                                List<UmsMemberLog> umsMemberLogs = umsMemberLogService.find(cellValue, 1, 1);
                                if(umsMemberLogs.size() != 0) {
                                    int cellNum = startCell + 1;
                                    UmsMemberLog umsMemberLog = umsMemberLogs.get(0);
                                    if (umsMemberLog.getStatus().equals("03")) {
                                        map.put("result", "9999");
                                        map.put("message", r + 1 + "行" + cellNum + "列" + ":" + "该手机号用户处于“审核中”，校验不通过!!");
                                        return map;
                                    }
                                }
                                break;
                            case 2:
                                if (cellValue.length() != 18) {
                                    int cellNum = startCell + 1;
                                    map.put("result", "9999");
                                    map.put("message", r + 1 + "行" + cellNum + "列" + ":" + "身份证号码填写不规范");
                                    return map;
                                }
                                break;
                            case 7:
                                List<UmsCommunity> umsCommunities = umsCommunityService.find(null, null, null, cellValue.trim(), 1, 1);
                                if (umsCommunities.size() == 0) {
                                    int cellNum = startCell + 1;
                                    map.put("result", "9999");
                                    map.put("message", r + 1 + "行" + cellNum + "列" + ":" + "本条数据中社区不存在，校验不通过");
                                    return map;
                                }
                                UmsCommunity umsCommunity = umsCommunities.get(0);
                                entityMemberValue = getEntityMemberValue(entityMemberValue, fields, f, umsCommunity.getId()+"");
                                // 赋值
                                PropertyUtils.setProperty(newInstance, "communityId", entityMemberValue);
                                break;
                            case 10:
                                String newPassword = cellValue.trim();
                                if (StrUtil.isNotEmpty(newPassword)) {
                                    cellValue = passwordEncoder.encode(newPassword);
                                }
                                break;
                            case 13:
                                cellValue = Mapping.getSplitId(cellValue);
                                if (StrUtil.isEmpty(cellValue)) {
                                    int cellNum = startCell + 1;
                                    map.put("result", "9999");
                                    map.put("message", r + 1 + "行" + cellNum + "列" + ":" + "本条数据中用户身份不存在，校验不通过");
                                    return map;
                                }
                                break;
                            default:
                                break;
                        }
                        entityMemberValue = getEntityMemberValue(entityMemberValue, fields, f, cellValue);
                        // 赋值
                        PropertyUtils.setProperty(newInstance, fieldName, entityMemberValue);
                        // 列的下标加1
                        startCell++;
                    }
                }

            }
            listt.add((T) newInstance);
        }
        map.put("result", "0000");
        map.put("message", "success");
        map.put("data", listt);
        return map;
    }
    /**
     * 根据Excel表格中的数据判断类型得到值
     *
     * @param cell
     * @return
     * @see [类、类#方法、类#成员]
     */
    private static String getCellValue(Cell cell) {
        String cellValue = "";

        if (null != cell) {
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                    if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                        Date theDate = cell.getDateCellValue();
                        SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
                        cellValue = dff.format(theDate);
                    } else {
                        DecimalFormat df = new DecimalFormat("0");
                        cellValue = df.format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING: // 字符串
                    cellValue = cell.getStringCellValue();
                    break;

                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    cellValue = cell.getBooleanCellValue() + "";
                    break;

                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                    cellValue = cell.getCellFormula() + "";
                    break;

                case HSSFCell.CELL_TYPE_BLANK: // 空值
                    cellValue = "";
                    break;

                case HSSFCell.CELL_TYPE_ERROR: // 故障
                    cellValue = "非法字符";
                    break;

                default:
                    cellValue = "未知类型";
                    break;
            }

        }
        return cellValue;
    }

    /**
     * 根据实体成员变量的类型得到成员变量的值
     *
     * @param realValue
     * @param fields
     * @param f
     * @param cellValue
     * @return
     * @see [类、类#方法、类#成员]
     */
    private static Object getEntityMemberValue(Object realValue, Field[] fields, int f, String cellValue) {
        String type = fields[f].getType().getName();
        switch (type) {
            case "char":
            case "java.lang.Character":
            case "java.lang.String":
                realValue = cellValue;
                break;
            case "java.util.Date":
                realValue = StringUtils.isBlank(cellValue) ? null : DateUtil.strToDate(cellValue, DateUtil.YYYY_MM_DD);
                break;
            case "java.lang.Integer":
                realValue = StringUtils.isBlank(cellValue) ? null : Integer.valueOf(cellValue);
                break;
            case "int":
            case "float":
            case "double":
            case "java.lang.Double":
            case "java.lang.Float":
            case "java.lang.Long":
            case "java.lang.Short":
            case "java.math.BigDecimal":
                realValue = StringUtils.isBlank(cellValue) ? null : new BigDecimal(cellValue);
                break;
            default:
                break;
        }
        return realValue;
    }

    /**
     * 根据路径或文件名选择Excel版本
     *
     * @param filePathOrName
     * @param in
     * @return
     * @throws IOException
     * @see [类、类#方法、类#成员]
     */
    public static Workbook chooseWorkbook(String filePathOrName, InputStream in) throws IOException {
        /** 根据版本选择创建Workbook的方式 */
        Workbook wb = null;
        boolean isExcel2003 = ExcelVersionUtil.isExcel2003(filePathOrName);

        if (isExcel2003) {
            wb = new HSSFWorkbook(in);
        } else {
            wb = new XSSFWorkbook(in);
        }

        return wb;
    }

    /**
     * 根据流读取Excel文件
     *
     * @param inputStream
     * @param isExcel2003
     * @return
     * @see [类、类#方法、类#成员]
     */
    public List<List<String>> read(InputStream inputStream, boolean isExcel2003) throws IOException {

        List<List<String>> dataLst = null;

        /** 根据版本选择创建Workbook的方式 */
        Workbook wb = null;

        if (isExcel2003) {
            wb = new HSSFWorkbook(inputStream);
        } else {
            wb = new XSSFWorkbook(inputStream);
        }
        dataLst = readDate(wb);

        return dataLst;
    }

    /**
     * 读取数据
     *
     * @param wb
     * @return
     * @see [类、类#方法、类#成员]
     */
    private List<List<String>> readDate(Workbook wb) {

        List<List<String>> dataLst = new ArrayList<List<String>>();

        /** 得到第一个shell */
        Sheet sheet = wb.getSheetAt(0);

        /** 得到Excel的行数 */
        totalRows = sheet.getPhysicalNumberOfRows();

        /** 得到Excel的列数 */
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }

        /** 循环Excel的行 */
        for (int r = 0; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }

            List<String> rowLst = new ArrayList<String>();

            /** 循环Excel的列 */
            for (int c = 0; c < getTotalCells(); c++) {

                Cell cell = row.getCell(c);
                String cellValue = "";

                if (null != cell) {
                    // 以下是判断数据的类型
                    switch (cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                            cellValue = cell.getNumericCellValue() + "";
                            break;

                        case HSSFCell.CELL_TYPE_STRING: // 字符串
                            cellValue = cell.getStringCellValue();
                            break;

                        case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                            cellValue = cell.getBooleanCellValue() + "";
                            break;

                        case HSSFCell.CELL_TYPE_FORMULA: // 公式
                            cellValue = cell.getCellFormula() + "";
                            break;

                        case HSSFCell.CELL_TYPE_BLANK: // 空值
                            cellValue = "";
                            break;

                        case HSSFCell.CELL_TYPE_ERROR: // 故障
                            cellValue = "非法字符";
                            break;

                        default:
                            cellValue = "未知类型";
                            break;
                    }
                }

                rowLst.add(cellValue);
            }

            /** 保存第r行的第c列 */
            dataLst.add(rowLst);
        }

        return dataLst;
    }

    static class ExcelVersionUtil {

        /**
         * 是否是2003的excel，返回true是2003
         *
         * @param filePath
         * @return
         * @see [类、类#方法、类#成员]
         */
        public static boolean isExcel2003(String filePath) {
            return filePath.matches("^.+\\.(?i)(xls)$");

        }

        /**
         * 是否是2007的excel，返回true是2007
         *
         * @param filePath
         * @return
         * @see [类、类#方法、类#成员]
         */
        public static boolean isExcel2007(String filePath) {
            return filePath.matches("^.+\\.(?i)(xlsx)$");

        }

    }

    public static class DateUtil {

        // ======================日期格式化常量=====================//

        public static final String YYYY_MM_DDHHMMSS = "yyyy-MM-dd HH:mm:ss";

        public static final String YYYY_MM_DD = "yyyy-MM-dd";

        public static final String YYYY_MM = "yyyy-MM";

        public static final String YYYY = "yyyy";

        public static final String YYYYMMDDHHMMSS = "yyyyMMddHHmmss";

        public static final String YYYYMMDD = "yyyyMMdd";

        public static final String YYYYMM = "yyyyMM";

        public static final String YYYYMMDDHHMMSS_1 = "yyyy/MM/dd HH:mm:ss";

        public static final String YYYY_MM_DD_1 = "yyyy/MM/dd";

        public static final String YYYY_MM_1 = "yyyy/MM";

        /**
         * 自定义取值，Date类型转为String类型
         *
         * @param date    日期
         * @param pattern 格式化常量
         * @return
         * @see [类、类#方法、类#成员]
         */
        public static String dateToStr(Date date, String pattern) {
            SimpleDateFormat format = null;

            if (null == date) {
                return null;
            }
            format = new SimpleDateFormat(pattern, Locale.getDefault());

            return format.format(date);
        }

        /**
         * 将字符串转换成Date类型的时间
         * <hr>
         *
         * @param s 日期类型的字符串<br>
         *          datePattern :YYYY_MM_DD<br>
         * @return java.util.Date
         */
        public static Date strToDate(String s, String pattern) {
            if (s == null) {
                return null;
            }
            Date date = null;
            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
            try {
                date = sdf.parse(s);
            } catch (ParseException e) {
                e.printStackTrace();
            }
            return date;
        }
    }

    /**
     * 读取列表数据 <按顺序放入带有注解的实体成员变量中>
     *
     * @param wb        工作簿
     * @param t         实体
     * @param beginLine 开始行数
     * @param totalcut  结束行数减去相应行数
     * @return List<T> 实体列表
     * @throws Exception
     * @see [类、类#方法、类#成员]
     */
    @SuppressWarnings("unchecked")
    public <T> Map<String, Object> readTrainingDataListT(Workbook wb, T t, int sheetAt, int beginLine, int totalcut, Map<String, Object> map) throws Exception {
        List<T> listt = new ArrayList<T>();

        /** 得到第一个shell */
        Sheet sheet = wb.getSheetAt(sheetAt);

        /** 得到Excel的行数 */
        totalRows = sheet.getPhysicalNumberOfRows();

        /** 得到Excel的列数 */
        if (totalRows >= 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        boolean flag = false;
        System.out.println("总行数：" + (sheet.getLastRowNum() + 1));
        for (int i = beginLine; i <= sheet.getLastRowNum(); ) {
            Row r = sheet.getRow(i);
            if (r == null) {
                // 如果是空行（即没有任何数据、格式），直接把它以下的数据往上移动
                sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
                continue;
            }
            flag = false;
            for (Cell c : r) {
                if (c.getCellType() != Cell.CELL_TYPE_BLANK) {
                    flag = true;
                    break;
                }
            }
            if (flag) {
                i++;
                continue;
            } else {//如果是空白行（即可能没有数据，但是有一定格式）
                if (i == sheet.getLastRowNum()) {
                    //如果到了最后一行，直接将那一行remove掉
                    sheet.removeRow(r);
                } else {
                    //如果还没到最后一行，则数据往上移一行
                    sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
                }
            }
        }
        totalRows = sheet.getLastRowNum() + 1;

        /** 循环Excel的行 */
        for (int r = beginLine - 1; r < totalRows - totalcut; r++) {

            Object newInstance = t.getClass().newInstance();
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }

            // 成员变量的值
            Object entityMemberValue = "";

            // 所有成员变量
            Field[] fields = t.getClass().getDeclaredFields();
            // 列开始下标
            int startCell = 0;

            for (int f = 0; f < fields.length; f++) {

                fields[f].setAccessible(true);
                String fieldName = fields[f].getName();
                boolean fieldHasAnno = fields[f].isAnnotationPresent(IsNeeded.class);
                // 有注解
                if (fieldHasAnno) {
                    IsNeeded annotation = fields[f].getAnnotation(IsNeeded.class);
                    boolean isNeeded = annotation.isNeeded();
                    // Excel需要赋值的列
                    if (isNeeded) {
                        Cell cell = row.getCell(startCell);
                        String cellValue = getCellValue(cell);
                        switch (startCell) {
                            case 2:
                                if (StringUtils.isEmpty(cellValue)) {
                                    int cellNum = startCell + 1;
                                    map.put("result", "9999");
                                    map.put("message", r + 1 + "行" + cellNum + "列" + ":" + "手机号不可为空");
                                    return map;
                                }
                                String substring = cellValue.substring(0, 1);
                                if (!substring.equals("1") || cellValue.length() != 11) {
                                    int cellNum = startCell + 1;
                                    map.put("result", "9999");
                                    map.put("message", r + 1 + "行" + cellNum + "列" + ":" + "手机号填写不规范");
                                    return map;
                                }
                                List<UmsMemberLog> umsMemberLogs = umsMemberLogService.find(cellValue, 1, 1);
                                if(umsMemberLogs.size() != 0) {
                                    int cellNum = startCell + 1;
                                    UmsMemberLog umsMemberLog = umsMemberLogs.get(0);
                                    if (umsMemberLog.getStatus().equals("03")) {
                                        map.put("result", "9999");
                                        map.put("message", r + 1 + "行" + cellNum + "列" + ":" + "该手机号用户处于“审核中”，校验不通过!!");
                                        return map;
                                    }
                                }
                                break;
                            case 3:
                                List<UmsCommunity> umsCommunities = umsCommunityService.find(null, null, null, cellValue.trim(), 1, 1);
                                if (umsCommunities.size() == 0) {
                                    int cellNum = startCell + 1;
                                    map.put("result", "9999");
                                    map.put("message", r + 1 + "行" + cellNum + "列" + ":" + "本条数据中社区不存在，校验不通过");
                                    return map;
                                }
                                UmsCommunity umsCommunity = umsCommunities.get(0);
                                entityMemberValue = getEntityMemberValue(entityMemberValue, fields, f, umsCommunity.getId()+"");
                                // 赋值
                                PropertyUtils.setProperty(newInstance, "communityId", entityMemberValue);
                                break;
                            default:
                                break;
                        }
                        entityMemberValue = getEntityMemberValue(entityMemberValue, fields, f, cellValue);
                        // 赋值
                        PropertyUtils.setProperty(newInstance, fieldName, entityMemberValue);
                        // 列的下标加1
                        startCell++;
                    }
                }

            }
            listt.add((T) newInstance);
        }
        map.put("result", "0000");
        map.put("message", "success");
        map.put("data", listt);
        return map;
    }

}